/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.p10.miage.gestionabsence.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 *
 * @author Sofian Seid
 */
public class EtudiantBDD {

   public EtudiantBDD() {
       
   }

    public static Etudiant trouverParId(int id) throws SQLException, Exception {
        Etudiant etudiant = new Etudiant();
        String sql = "SELECT * FROM ETUDIANT WHERE ID = ?";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, id);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                etudiant.init(resultat);
            } 
            resultat.close();
        }
        return etudiant;
    }
       
    public static ArrayList getList(int id) throws SQLException {
        ArrayList <Etudiant> etudiants = new ArrayList<Etudiant>();    
        String sql="SELECT * FROM ETUDIANT WHERE PROMOTION_ID = ?";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1,id);
        ResultSet resultat = pst.executeQuery();       
        while(resultat.next()){
            etudiants.add(new Etudiant(resultat));
        }
        resultat.close();
        return etudiants;    
    }
    
    public static Etudiant trouverParLoginEtPassword(String login, String password) throws SQLException, Exception {
      Etudiant etudiant = new Etudiant();
      String sql = "SELECT * FROM ETUDIANT WHERE MAIL = ?";
      PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
      pst.setString(1, login);
      if (pst.execute()) {
         ResultSet resultat = pst.getResultSet();
         if (resultat.first() == true) {
            if (password.equals(resultat.getString("PASSWD"))) {
               etudiant.init(resultat);
            } else {
               throw new Exception("Mot de passe incorrect");
            }
         } else {
            throw new Exception("Utilisateur inconnu");
         }
         resultat.close();
      }
      return etudiant;
   }

    public static Etudiant signalerPresenceCours(int idEtudiant, int idAgenda) throws SQLException, Exception {
        Etudiant etudiant = new Etudiant();
        String sql = "INSERT INTO PRESENCE VALUES(?,?)";
        PreparedStatement pst = Connection.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
        pst.setInt(1, idEtudiant);
        pst.setInt(2, idAgenda);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if(resultat.first()==true) {
                if(resultat.last()==true) {
                    etudiant.init(resultat);
                } else {
                    throw new Exception("ID Agenda incorrect");
                }
            } else {
                throw new Exception("ID Etudiant incorrect");
            }
            resultat.close();
        }
        return etudiant;
    }
}
